import pandas as pd
import os
import csv
from openpyxl import Workbook


def compiler_excel(file_path=""):
    """
    读取Excel文件并转换为JSON对象数组
    相当于JavaScript中的complierExcel函数
    """
    print(f"读取文件: {file_path}")
    # 读取Excel文件
    df = pd.read_excel(file_path)
    
    # 获取第一行作为映射
    map_row = df.iloc[0].to_dict()
    
    # 创建新的数组（列表）
    new_arr = []
    
    # 从第二行开始处理数据
    for i in range(1, len(df)):
        item = df.iloc[i].to_dict()
        obj = {}
        for key in item.keys():
            if key in map_row and not pd.isna(map_row[key]):
                label = map_row[key]
                # 处理包含"支付订单号"的标签
                if isinstance(label, str) and "支付订单号" in label:
                    label = "支付订单号"
                obj[label] = item[key]
        new_arr.append(obj)
    
    return new_arr


def export_excel(data_sheets, export_file_name):
    """
    导出数据到Excel文件
    相当于JavaScript中的exportExcel函数
    """
    # 创建一个新的Excel工作簿
    workbook = Workbook()
    
    # 如果传入的是单个数据集而非对象
    if not isinstance(data_sheets, dict) or 'sheets' not in data_sheets:
        # 获取默认的工作表
        worksheet = workbook.active
        if worksheet:
            worksheet.title = "Sheet1"
        
        # 添加表头
        if data_sheets and len(data_sheets) > 0:
            headers = list(data_sheets[0].keys())
            for col_idx, header in enumerate(headers, 1):
              worksheet.cell(row=1, column=col_idx, value=header)
            
            # 添加数据
            for row_idx, row_data in enumerate(data_sheets, 2):
                for col_idx, header in enumerate(headers, 1):
                    worksheet.cell(row=row_idx, column=col_idx, value=row_data.get(header, ""))
    else:
        # 处理多个工作表
        # 删除默认创建的Sheet
        default_sheet = workbook.active
        if default_sheet is not None:
            workbook.remove(default_sheet)
        
        for sheet in data_sheets['sheets']:
            # 创建新的工作表
            worksheet = workbook.create_sheet(title=sheet['name'])
            
            # 添加表头
            if sheet['data'] and len(sheet['data']) > 0:
                headers = list(sheet['data'][0].keys())
                for col_idx, header in enumerate(headers, 1):
                    worksheet.cell(row=1, column=col_idx, value=header)
                
                # 添加数据
                for row_idx, row_data in enumerate(sheet['data'], 2):
                    for col_idx, header in enumerate(headers, 1):
                        worksheet.cell(row=row_idx, column=col_idx, value=row_data.get(header, ""))
    
    # 保存工作簿
    workbook.save(export_file_name)
    print(f"Excel 文件导出成功！{export_file_name}")

import os

def create_filename(filename):
    name = filename
    count = 1
    base_name, extension = os.path.splitext(filename)
    while os.path.exists(name):
        name = f"{base_name}({count}){extension}"
        count += 1
    return name

def get_csv_data(file_path="../PayPal2025.1.1-3.31.CSV"):
    """
    读取CSV文件数据
    相当于JavaScript中的getCSVData函数
    """
    results = []
    try:
        with open(file_path, 'r', encoding='utf-8') as csvfile:
            csv_reader = csv.DictReader(csvfile)
            for row in csv_reader:
                results.append(row)
        print(f"CSV 读取完成，结果：{len(results)}")
        return results
    except Exception as e:
        print(f"读取错误: {e}")
        return []


def main():
    # 获取当前脚本所在目录
    current_dir = os.path.dirname(os.path.abspath(__file__))
    
    # 读取Excel文件
    file1 = os.path.join(current_dir, "NW2025.1.1-3.31全部.xlsx")
    file2 = os.path.join(current_dir, "NW2025.1.1-3.31已存档.xlsx")
    
    # 编译Excel数据
    data1 = compiler_excel(file1)
    data2 = compiler_excel(file2)
    new_data = data1 + data2  # 相当于JavaScript中的concat
    
    # 定义表头
    header = [
        "支付订单号",
        "货币单位",
        "订单总价",
        "商品总价",
        "优惠金额",
        "物流费用",
        "支付状态",
        "支付方式1",
        "支付渠道1",
        "支付金额1",
    ]
    
    # 筛选条件1：部分退款或已支付，且支付渠道不是空中云汇
    pay_data = []
    for item in new_data:
        if item.get("支付状态") in ["部分退款", "已支付"] and item.get("支付渠道1") != "空中云汇":
            obj = {}
            for key in header:
                obj[key] = item.get(key, "")
            pay_data.append(obj)
    
    # 筛选条件2：对应云汇
    air_data = []
    for item in new_data:
        if item.get("支付状态") in ["部分退款", "已支付"] and item.get("支付渠道1") == "空中云汇":
            obj = {}
            for key in header:
                obj[key] = item.get(key, "")
            air_data.append(obj)
    
    # 读取CSV数据
    csv_file = os.path.join(current_dir, "PayPal2025.1.1-3.31.CSV")
    csv_data = get_csv_data(csv_file)
    # 读取云汇CSV数据
    csv_file2 = os.path.join(current_dir, "air2025.1.1-3.31.csv")
    csv_data2 = get_csv_data(csv_file2)
    
    # 定义CSV数据的表头
    csv_header = ["交易号", "类型", "状态", "币种", "总额", "费用", "净额"]
    checkout_types = ["快速结账付款", "直接信用卡付款", "预先批准付款账单用户付款"]

    csv_header2 = ["交易编号", "授权状态返回描述", "交易状态", "交易币种", "交易金额"]
    checkout_types2 = ["FULLY REFUNDED", "PARTIALLY REFUNDED", "SUCCEEDED"]
    # 处理PayPal数据
    pay_data2 = []
    for item in pay_data:
        is_repeat = False
        for csv_item in csv_data:
            is_same = csv_item.get("交易号") == item.get("支付订单号")
            if is_same:
                if csv_item.get("状态") == "已完成" and csv_item.get("类型") in checkout_types:
                    new_obj = {}
                    for key in csv_header:
                        new_obj[key] = csv_item.get(key, "")
                        # 将数值字段转换为浮点数
                        if key in ["总额", "费用", "净额"]:
                            try:
                                new_obj[key] = float(new_obj[key])
                            except (ValueError, TypeError):
                                new_obj[key] = 0.0
                    
                    new_obj["计数"] = 1
                    if is_repeat:
                        # print(f"重复 {csv_item.get('交易号')}")
                        new_obj["计数"] = new_obj.get("计数", 0) + 1
                    
                    is_repeat = True
                    pay_data2.append(new_obj)
    
    # 处理云汇数据
    air_data2 = []
    for item in air_data:
        # for csv_item in csv_data2:
        #     is_same = csv_item.get("交易编号") == item.get("支付订单号")
        #     if is_same:
        #         if  csv_item.get("交易状态") in checkout_types2:
        #             air_data2.append(csv_item)
        is_repeat = False
        for csv_item in csv_data2:
            is_same = csv_item.get("交易编号") == item.get("支付订单号")
            if is_same:
                if csv_item.get("交易状态") in checkout_types2:
                    new_obj = {}
                    for key in csv_header2:
                        new_obj[key] = csv_item.get(key, "")
                        # 将数值字段转换为浮点数
                        if key in ["交易金额"]:
                            try:
                                new_obj[key] = float(new_obj[key])
                            except (ValueError, TypeError):
                                new_obj[key] = 0.0
                    
                    new_obj["计数"] = 1
                    if is_repeat:
                        # print(f"重复 {csv_item.get('交易号')}")
                        new_obj["计数"] = new_obj.get("计数", 0) + 1
                    
                    is_repeat = True
                    air_data2.append(new_obj)
    
    # 创建多工作表的Excel文件
    multi_sheet_data = {
        'sheets': [
            {"name": "原始数据", "data": new_data},
            {"name": "PayPal订单", "data": pay_data},
            {"name": "csv-PayPal", "data": pay_data2},
            {"name": "云汇订单", "data": air_data},
            {"name": "csv-云汇订单", "data": air_data2},
        ]
    }
    
    # 导出多工作表Excel文件
    output_name = create_filename("NW2025.1.1-3.31_all.xlsx")
    output_file = os.path.join(current_dir, output_name)
    export_excel(multi_sheet_data, output_file)


if __name__ == "__main__":
    main()